Release 10.1A: OpenEdge Data Management:
SQL Development

Types of integrity constraints

SQL provides four types of integrity constraints:

SQL allows you to specify an integrity constraint, and to refer to that constraint in other SQL statements. The database assigns a constraint name if you do not specify one.

Example 5–11 shows the assignment of table constraint prim_constr on table supplier_item. You specify a constraint name with the CONSTRAINT keyword.

Example 5–11: Constraint modifier 
 CREATE TABLE supplier_item ( 
          supp_no  INTEGER NOT NULL, 
          item_no  INTEGER NOT NULL, 
          qty      INTEGER NOT NULL DEFAULT 0 
          CONSTRAINT prim_constr 
          PRIMARY KEY (supp_no, item_no) 
     ) ;

Check constraints

The values you enter for a row must be valid so that the data in the database is consistent. For example, the city names you enter into the supplier table must correspond to one of the cities where the suppliers are located. The database checks to ensure that each value corresponds to one of the valid city names. You achieve these validations by specifying check constraints during the definition of the table schema. Use check constraints when you want to restrict a column to a set of valid values.

Example 5–12 shows how to specify a check constraint on the supplier table. In this example, the city column is defined with a check constraint to verify that values for city are in the set of NEW YORK, BOSTON, DALLAS, or MANCHESTER. This CREATE statement does not use the CONSTRAINT keyword in the table definition. The system assigns a constraint name.

Example 5–12: Checking a constraint 
 CREATE TABLE supplier ( 
          supp_no  INTEGER NOT NULL, 
          last_name  CHAR (30), 
          status SMALLINT, 
          city  CHAR (20) CHECK ( 
          supplier.city IN ('NEW YORK', 'BOSTON', 'DALLAS', 'MANCHESTER')) 
     ) ;

A check constraint on a table specifies a condition on the column values of a row in that table. Whenever you issue an INSERT or UPDATE statement against a table containing check constraints, the database validates the column values. The INSERT or UPDATE operation is completed only after successful validation.

You can specify a check constraint at either the column level or the table level.

Column-level check constraints

In an application, you might decide to check a particular column for valid data whenever you attempt to INSERT or UPDATE values for that column. For example, you design your database to disallow suppliers from a place called Toxic Island. Use a column-level check constraint for this type of validation.

In Example 5–13, there is a column-level check constraint on the city column of the supplier table; this check constraint affects the city column only. When you issue an INSERT or UPDATE operation against the supplier table involving the city column, the SQL engine validates the column value, ensuring that the column does not contain the value 'Toxic Island'. If the INSERT or UPDATE statement violates the check condition, the database returns a constraint violation error.

Example 5–13: Column-level check constraint 
CREATE TABLE supplier ( 
     supp_no  INTEGER NOT NULL, 
     last_name  CHAR (30), 
     status SMALLINT, 
     city  CHAR (20) CHECK ( 
     supplier.city <> 'Toxic Island') 
     ) ;

In Example 5–14 and Example 5–15, the INSERT statement results in an error, and the corresponding row is not inserted into the table.

Example 5–14: INSERT failure due to check constraint 
 INSERT INTO supplier VALUES (1001, 'Worm', 20, 'Toxic Island') ;

Example 5–15: Constraint violation error message 
=== SQL Exception 1 === 
SQLState= 
ErrorCode=-20116 
[JDBC Progress Driver]:Constraint violation (7597)

Table-level check constraints

Your application might be required to enforce rules on multiple columns. To specify a constraint on more than one table column, define the constraint at the table level. For example, you might need to enforce a validation check on both the status and the city columns in the supplier table.

In Example 5–16, the table-level check constraint verifies that when the city is CHICAGO, the status must be 20, otherwise the operation returns a table-level check constraint violation.

Example 5–16: Table-level check constraint 
 CREATE TABLE supplier ( 
     supp_no    INTEGER NOT NULL, 
     last_name       CHAR (30), 
     status     SMALLINT CHECK ( 
          supplier.status BETWEEN 1 AND 100 ), 
     city       CHAR (20) 
     CHECK ( 
          supplier.city IN ('NEWYORK', 'BOSTON', 'CHICAGO', 'MANCHESTER' ) 
            ), 
     CHECK (supplier.city <> 'CHICAGO' OR supplier.status = 20) 
) ;

Since the check constraint specification involves more than one column, you must specify it at the table level. If an INSERT or UPDATE statement violates the check condition, the database returns an error.

Example 5–17 shows an INSERT statement for the supplier table created in the previous example. This INSERT operation results in a check constraint violation.

Example 5–17: Table-level check constraint violation 
 INSERT INTO supplier VALUES (1001, 'John', 40, 'CHICAGO') ;

Primary keys

A primary key consists of one or more columns in a table that uniquely identifies each row. For example, the supp_no column value in the supplier table must be unique. Every row of the table is uniquely identified by this column value. A table can contain only one primary key constraint. If you supply a duplicate value for a primary key column in an INSERT operation, the operation returns an error.

You can design your database table so that there is only one column that distinguishes a given row from other rows. In this case, a single column is the unique identifier of the table. For example, the supp_no column is a primary key for the supplier table. Primary key constraints are defined in the column definitions of a table.

In Example 5–18, the supp_no column is a unique identifier in the supplier table, and the key consists of only one column. This example shows how to create a column-level primary key on the supplier table.

Example 5–18: Column-level primary key 
 CREATE TABLE supplier 
          ( 
          supp_no  INTEGER NOT NULL PRIMARY KEY, 
          last_name  CHAR (30), 
          status  SMALLINT, 
          city  CHAR (20) 
          ) ;

Candidate keys

If you design a table to require that a column or combination of columns define a row as unique, you define the columns with a candidate key constraint.

In Example 5–19, the employee number (empno) is the primary key in the employee table because it uniquely identifies each row. Each entry in the employee social security column must also be distinct. Because a primary key has been designated already for the table, you must place a candidate key constraint on the ss_no column.

Example 5–19: Primary key 
 CREATE TABLE employee ( 
          empno  INTEGER NOT NULL PRIMARY KEY, 
          ss_no  INTEGER NOT NULL UNIQUE, 
          ename  CHAR (19), 
          sal  NUMERIC (10, 2), 
          deptno  INTEGER NOT NULL 
     ) ;

You declare a column as a candidate key by using the keyword UNIQUE. Precede the UNIQUE keyword with the NOT NULL specification. Like a primary key, a candidate key also uniquely identifies a row in a table. Note that a table can have only one primary key, but can have any number of candidate keys.

If you supply a duplicate value for a candidate key in an INSERT or UPDATE operation, the operation returns an error.

Copyright © 2005 Progress Software Corporation
 www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095